package manage.service.impl;

import manage.entity.StudentDo;
import manage.req.StudentRequest;
import manage.res.TableDTO;
import manage.service.StudentService;
import manage.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

/**
 * StudentService的实现类
 */
public class StudentServiceImpl implements StudentService {
    @Override
    public TableDTO retrieveStudents(StudentRequest request) {
        StringBuilder sql=new StringBuilder();
        sql.append("select * from student ");
        if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
            sql.append(" where name like '%"+request.getSearchKey().trim()+"%' ");
        }
        //分页查询
        sql.append("order by id asc limit ").append(request.getStart()).append(",").append(request.getPageSize());
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        TableDTO returnDTO=new TableDTO();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            //查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from student ");
            if(request.getSearchKey()!=null&&!"".equals(request.getSearchKey().trim())){
                sql.append(" where name like '%"+request.getSearchKey().trim()+"%' ");
            }
            ps=conn.prepareStatement(sql.toString());
            rs= ps.executeQuery();
            while(rs.next()){
                int count=rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;

        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean add(StudentDo studentDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" insert into student(name,sex,class,Chinese,Math,English) ");
        sql.append(" values(?,?,?,?,?,?) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1,studentDo.getName());
            ps.setString(2,studentDo.getSex());
            ps.setString(3,studentDo.getGrade());
            ps.setInt(4,studentDo.getChinese());
            ps.setInt(5,studentDo.getMath());
            ps.setInt(6,studentDo.getEnglish());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public StudentDo getById(int selectedStudentId) {
        StringBuilder sql=new StringBuilder("select * from student where id=? ");
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        StudentDo studentDo=new StudentDo();
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setInt(1,selectedStudentId);
            rs=ps.executeQuery();

            while(rs.next()){
                //处理每一条查询
                int id= rs.getInt("id");
                String name= rs.getString("name");
                String sex= rs.getString("sex");
                String grade= rs.getString("class");
                int Chinese= rs.getInt("Chinese");
                int Math= rs.getInt("Math");
                int English= rs.getInt("English");
                studentDo.setId(id);
                studentDo.setName(name);
                studentDo.setSex(sex);
                studentDo.setGrade(grade);
                studentDo.setChinese(Chinese);
                studentDo.setMath(Math);
                studentDo.setEnglish(English);
            }

            return studentDo;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean update(StudentDo studentDo) {
        StringBuilder sql=new StringBuilder();
        sql.append(" update student set name=?,sex=?,class=?,Chinese=?,Math=?,English=? ");
        sql.append(" where id =? ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());
            ps.setString(1,studentDo.getName());
            ps.setString(2,studentDo.getSex());
            ps.setString(3,studentDo.getGrade());
            ps.setInt(4,studentDo.getChinese());
            ps.setInt(5,studentDo.getMath());
            ps.setInt(6,studentDo.getEnglish());
            ps.setInt(7,studentDo.getId());
            return ps.executeUpdate()==1;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    @Override
    public boolean delete(int[] selectedStudentIds) {
        StringBuilder sql=new StringBuilder();
        sql.append(" delete from student where id in ( ");
        for (int i = 0; i < selectedStudentIds.length; i++) {
            if(i==selectedStudentIds.length-1){
                sql.append(" ? ");
            }else{
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            conn=DBUtil.getConn();
            ps=conn.prepareStatement(sql.toString());

            for (int i = 0; i < selectedStudentIds.length; i++) {
                //设置参数,从1开始
                ps.setInt(i+1,selectedStudentIds[i]);
            }
            return ps.executeUpdate()==selectedStudentIds.length;
        }catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
    }

    private static Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>>data =new Vector<>();
        while(rs.next()){
            //处理每一条查询
            Vector<Object>oneRecord=new Vector<>();
            int id= rs.getInt("id");
            String name= rs.getString("name");
            String sex= rs.getString("sex");
            String grade= rs.getString("class");
            int Chinese= rs.getInt("Chinese");
            int Math= rs.getInt("Math");
            int English= rs.getInt("English");
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(sex);
            oneRecord.addElement(grade);
            oneRecord.addElement(Chinese);
            oneRecord.addElement(Math);
            oneRecord.addElement(English);
            data.addElement(oneRecord);
        }
        return data;
    }

}
